﻿using System;
using System.Collections.Generic;

using System.Configuration;
using System.Data;
using DataBaseInfo;
using DataBaseManger;

namespace SQLServerHelper
{
    public class SQLServerManger
    {
        private string _SQLServer;
        private string _LoginName;
        private string _LoginPwd;
        private readonly string _ConnectionString;
        public SQLServerManger(){}
        public SQLServerManger(string sSQLServer, string sLoginName, string sLoginPwd)
        {
            this._SQLServer = sSQLServer;
            this._LoginName = sLoginName;
            this._LoginPwd = sLoginPwd;
            this._ConnectionString=string.Format("Server={0};Database=$DataBase$;uid={1};pwd={2}",sSQLServer,sLoginName,sLoginPwd);
        }

        private string GetConnectionServerString(string sDataBase)
        {
            return _ConnectionString.Replace("$DataBase$", sDataBase);
        }

        /// <summary>
        /// 取得所有SQLServer 的服务的服务器列表
        /// </summary>
        /// <returns></returns>
        public List<string> GetSQLServicesList()
        {
            SQLDMO.Application oSQLServerApp = new SQLDMO.Application();
            SQLDMO.NameList SQLServers = oSQLServerApp.ListAvailableSQLServers();
            List<string> aServices = new List<string>();
            int count = SQLServers.Count;
            if (count> 0)
            {
                for (int i = 0; i < count; i++)
                {
                    aServices.Add(SQLServers.Item(i + 1));
                }
                return aServices;
            }
            return null;
        }

        /// <summary>
        /// 取得当前连接的所有数据库+表+列 信息
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, List<Table>> GetDataBaseList()
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass(); 
            SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass(); 
            srv.Connect(_SQLServer,_LoginName,_LoginPwd);
            List<string> aExceptList = GetExceptInfo("Except_DatabaseList"); 
            Dictionary<string, List<Table>> oDataBase = new Dictionary<string, List<Table>>();
            foreach (SQLDMO.Database db in srv.Databases)
            {
                if (!aExceptList.Contains(db.Name))
                {
                    List<Table> aTable = GetTable(db.Name);
                    oDataBase.Add(db.Name, aTable);
                }
            }

            return oDataBase;
        }

        /// <summary>
        ///  读取配置文件中排除的数据库
        /// </summary>
        /// <returns></returns>
        private List<string> GetExceptInfo(string exceptKey)
        {
            List<string> aExcept = new List<string>();
            string sExceptDatabaseList = ConfigurationManager.AppSettings[exceptKey];
            string[] aExceptlist = sExceptDatabaseList.Split(',');
            foreach (string s in aExceptlist)
            {
                if (!aExcept.Contains(s))
                    aExcept.Add(s);
            }
            return aExcept;
        }

        /// <summary>
        /// 取得 相关数据库中的所有表
        /// </summary>
        /// <param name="sDataBase">数据库名</param>
        /// <returns></returns>
        public List<Table> GetTable(string sDataBase)
        {
            string sSQL = "SELECT [name] FROM sysobjects WHERE type='u' AND name<>'dtproperties' And [name] Not In ('{0}')";

            SqlServerHelper oSQLHelper = new SqlServerHelper(GetConnectionServerString(sDataBase));
            oSQLHelper.Open();
            try
            {
                List<string> aExceptList = GetExceptInfo("Except_TableList");
                string sExceipt = string.Empty;
                foreach (string s in aExceptList)
                {
                    sExceipt += s + ",";
                }
                sExceipt = sExceipt.TrimEnd(',');
                sSQL = string.Format(sSQL, sExceipt);

                List<Table> aTableList = new List<Table>();
                DataSet oDS = oSQLHelper.ExecuteQuery(sSQL);
                if (oDS.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < oDS.Tables[0].Rows.Count; i++)
                    {
                        Table oTable = new Table();
                        oTable.TableName = oDS.Tables[0].Rows[i][0].ToString();
                        oTable.AddColumn(GetColumn(oSQLHelper, oTable.TableName));
                        aTableList.Add(oTable);
                    }
                }
                return aTableList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                oSQLHelper.Close();
                oSQLHelper.Dispose();
            }
        }
        
        /// <summary>
        /// 取得 数据库.表.下面的所有列名
        /// </summary>
        /// <param name="oSQLHelper"></param>
        /// <param name="sTable"></param>
        /// <returns></returns>
        public List<Column> GetColumn(SqlServerHelper oSQLHelper, string sTable)
        {
            string sSQL = string.Format("SELECT C.[name] ColumnName,T.[name] TypeName,C.xusertype ColumnTypeID,C.[length]" +
                                        " FROM syscolumns C WITH(NOLOCK)" +
                                        " INNER JOIN systypes T WITH(NOLOCK) ON C.xusertype=T.xusertype" +
                                        " Where C.id=object_id('{0}')", sTable);
            try
            {

                List<Column> aColumn = new List<Column>();
                DataSet oDSColumn = oSQLHelper.ExecuteQuery(sSQL);
                int count = oDSColumn.Tables[0].Rows.Count;
                if (count > 0)
                {
                    for (int i = 0; i < count; i++)
                    {
                        Column oColumn = new Column()
                        {
                            ClumnName = oDSColumn.Tables[0].Rows[i][0].ToString(),
                            ClumnType = oDSColumn.Tables[0].Rows[i][1].ToString()
                        };
                        int.TryParse(oDSColumn.Tables[0].Rows[i][2].ToString(), out oColumn.ColumnTypeID);
                        int.TryParse(oDSColumn.Tables[0].Rows[i][3].ToString(), out oColumn.ClumnSize);
                        aColumn.Add(oColumn);
                    }
                }
                return aColumn;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

    }
}
